Reading data excel file
library(xlsx)
library(stringr)
data = read.xlsx2("..\\data\\morocco_MRIO.xlsx",header=TRUE,sheetName = "IIOS")
original_data<-data
Here’s the initial data
original_data
First of all, you will notice that we cannot create the graph that we need from this data. In fact we need to create a squared matrix with the node the sectors of each region as nodes plus other nodes (See the article for more details).
Here is what these regions and sectors refere to:
| Symbol | Region |
|---|---|
| R1 | Tanger-Tetouan-Al Hoceima |
| R2 | Oriental |
| R3 | Fès-Meknès |
| R4 | Rabat-Salé-Kénitra |
| R5 | Béni Mellal-Khénifra |
| R6 | Grand Casablanca-Settat |
| R7 | Marrakech-Safi |
| R8 | Drâa-Tafilalet |
| R9 | Souss-Massa |
| R10 | Guelmim-Oued Noun |
| R11 | Laayoune-Sakia El Hamra |
| R12 | Dakhla-Oued Eddahab |
| Symbol | French Name | English Name |
|---|---|---|
| A00 | Agriculture, forêt et services annexes | Agriculture, forestry, hunting, related services |
| B05 | Pêche, aquaculture | Fishing, aquaculture |
| C00 | Industrie d’extraction | Mining industry |
| D01 | Industries alimentaires et tabac | Food industry and tobacco |
| D02 | Industries du textile et du cuir | Textile and leather industry |
| D03 | Industrie chimique et parachimique | Chemical and para-chemical industry |
| D04 | Industrie mécanique, métallurgique et électrique | Mechanical, metallurgical and electrical industry |
| D05 | Autres industries manufac. hors raffinage pétrole | Other manufacturing, excluding petroleum refining |
| D06 | Raffinage de pétrole et autres produits d’énergie | Oil refining and other energy products |
| E00 | Electricité et eau | Electricity and water |
| F45 | Bâtiment et travaux | publics Construction |
| G00 | Commerce | Trade |
| H55 | Hôtels et restaurants | Hotels and restaurants |
| I01 | Transports | Transport |
| I02 | Postes et télécommunications | Post and telecommunications |
| J00 | Activités financières et assurances | Financial activities and insurance |
| K00 | Immobilier, location et serv. rendus entreprises | Real estate, renting and services to enterprises |
| L75 | Administration publique et sécurité sociale | General public administration and social security |
| MNO | Education, santé et action sociale | Education, health and social action |
| OP0 | Autres services non financiers | Other non-financial services |
we will start by adding the symbols of the regions to the nodes
data<-original_data
for (i in 1:12){
start=(i-1)*20+1
end=i*20
for(j in start:end){
names(data)[j+1] <- paste(str_remove(names(data)[j+1],"\\.[0-9]*$"),paste("_R",i,sep=""),sep="")
}
}
data
NA
Naming investement demand as INV_D
start_inv_d=12*20+2
end_inv_d=start_inv_d+11
i=1
for(j in start_inv_d:end_inv_d){
names(data)[j] <- paste("INV_D_R",i,sep="")
i=i+1
}
Naming Household demand as HO_D
start_ho_d=end_inv_d+1
end_ho_d=start_ho_d+11
i=1
for(j in start_ho_d:end_ho_d){
names(data)[j] <- paste("HO_D_R",i,sep="")
i=i+1
}
Naming Governoment demand as GOV_D
start_gov_d=end_ho_d+1
end_gov_d=start_gov_d+11
i=1
for(j in start_gov_d:end_gov_d){
names(data)[j] <- paste("GOV_D_R",i,sep="")
i=i+1
}
Naming Foreign exports as FO_EXP
end_for_exp<-ncol(data)
names(data)[end_for_exp] <- "FO_EXP"
data
The purpose of this section is to make the data frame squared for graph construction
Adding the values added
end=12*20+1
squared_data=data.frame(data[,2:end],stringsAsFactors=FALSE)
squared_data
We will create theses duplicates so that if we want to re-run the cell code we’ll not have to run the entire code from the bigenning (note that the data changes)
squared_data_dup=squared_data
Here we want to make the matrix squared by adding zeros
Adding foreign investement
squared_data=squared_data_dup
start=ncol(squared_data)+1
end=start+19
for (i in start:end){
squared_data=cbind(squared_data,colname=as.factor(numeric(nrow(squared_data))))
names(squared_data)[ncol(squared_data)] <- paste(data[i,1],"_FOR",sep="")
}
squared_data_dup2=squared_data
Naming and adding taxes and subsidies, labor payments, capital payments, other costs, value added (resp.) as TAX_SUB, LA_PAY, CA_PAY, OTH_COT, VA_ADD
squared_data=squared_data_dup2
for (i in 1:5){
squared_data=cbind(squared_data,colname=numeric(nrow(squared_data)))
switch(i,
names(squared_data)[ncol(squared_data)] <- "TAX_SUB",
names(squared_data)[ncol(squared_data)] <- "LA_PAY",
names(squared_data)[ncol(squared_data)] <- "CA_PAY",
names(squared_data)[ncol(squared_data)] <- "OTH_COT",
names(squared_data)[ncol(squared_data)] <- "VA_ADD",
)
}
squared_data
squared_data_dup3=squared_data
squared_data=squared_data_dup3
squared_data=cbind(squared_data,data[,start_inv_d:end_for_exp])
squared_data
n=302-265
m_zeros=matrix(data=0,nrow=n,ncol = ncol(squared_data))
squared_data=data.frame(rbind(as.matrix(squared_data), as.matrix(m_zeros)))
squared_data
Now we will fill the matrix with the actual values of the added columns.
First we will need to convert the data to numeric in order to avoid any casting/levels problems.
Converting data to numeric
squared_data[] <- lapply(squared_data, function(x) {
if(is.factor(x)) as.numeric(as.character(x)) else x
})
sapply(squared_data, class)
A00_R1 B05_R1 C00_R1 D01_R1 D02_R1 D03_R1 D04_R1
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
D05_R1 D06_R1 E00_R1 F45_R1 G00_R1 H55_R1 I01_R1
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
I02_R1 J00_R1 K00_R1 L75_R1 MNO_R1 OP0_R1 A00_R2
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
B05_R2 C00_R2 D01_R2 D02_R2 D03_R2 D04_R2 D05_R2
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
D06_R2 E00_R2 F45_R2 G00_R2 H55_R2 I01_R2 I02_R2
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
J00_R2 K00_R2 L75_R2 MNO_R2 OP0_R2 A00_R3 B05_R3
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
C00_R3 D01_R3 D02_R3 D03_R3 D04_R3 D05_R3 D06_R3
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
E00_R3 F45_R3 G00_R3 H55_R3 I01_R3 I02_R3 J00_R3
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
K00_R3 L75_R3 MNO_R3 OP0_R3 A00_R4 B05_R4 C00_R4
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
D01_R4 D02_R4 D03_R4 D04_R4 D05_R4 D06_R4 E00_R4
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
F45_R4 G00_R4 H55_R4 I01_R4 I02_R4 J00_R4 K00_R4
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
L75_R4 MNO_R4 OP0_R4 A00_R5 B05_R5 C00_R5 D01_R5
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
D02_R5 D03_R5 D04_R5 D05_R5 D06_R5 E00_R5 F45_R5
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
G00_R5 H55_R5 I01_R5 I02_R5 J00_R5 K00_R5 L75_R5
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
MNO_R5 OP0_R5 A00_R6 B05_R6 C00_R6 D01_R6 D02_R6
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
D03_R6 D04_R6 D05_R6 D06_R6 E00_R6 F45_R6 G00_R6
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
H55_R6 I01_R6 I02_R6 J00_R6 K00_R6 L75_R6 MNO_R6
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
OP0_R6 A00_R7 B05_R7 C00_R7 D01_R7 D02_R7 D03_R7
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
D04_R7 D05_R7 D06_R7 E00_R7 F45_R7 G00_R7 H55_R7
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
I01_R7 I02_R7 J00_R7 K00_R7 L75_R7 MNO_R7 OP0_R7
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
A00_R8 B05_R8 C00_R8 D01_R8 D02_R8 D03_R8 D04_R8
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
D05_R8 D06_R8 E00_R8 F45_R8 G00_R8 H55_R8 I01_R8
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
I02_R8 J00_R8 K00_R8 L75_R8 MNO_R8 OP0_R8 A00_R9
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
B05_R9 C00_R9 D01_R9 D02_R9 D03_R9 D04_R9 D05_R9
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
D06_R9 E00_R9 F45_R9 G00_R9 H55_R9 I01_R9 I02_R9
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
J00_R9 K00_R9 L75_R9 MNO_R9 OP0_R9 A00_R10 B05_R10
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
C00_R10 D01_R10 D02_R10 D03_R10 D04_R10 D05_R10 D06_R10
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
E00_R10 F45_R10 G00_R10 H55_R10 I01_R10 I02_R10 J00_R10
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
K00_R10 L75_R10 MNO_R10 OP0_R10 A00_R11 B05_R11 C00_R11
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
D01_R11 D02_R11 D03_R11 D04_R11 D05_R11 D06_R11 E00_R11
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
F45_R11 G00_R11 H55_R11 I01_R11 I02_R11 J00_R11 K00_R11
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
L75_R11 MNO_R11 OP0_R11 A00_R12 B05_R12 C00_R12 D01_R12
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
D02_R12 D03_R12 D04_R12 D05_R12 D06_R12 E00_R12 F45_R12
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
G00_R12 H55_R12 I01_R12 I02_R12 J00_R12 K00_R12 L75_R12
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
MNO_R12 OP0_R12 A00_FOR B05_FOR C00_FOR D01_FOR D02_FOR
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
D03_FOR D04_FOR D05_FOR D06_FOR E00_FOR F45_FOR G00_FOR
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
H55_FOR I01_FOR I02_FOR J00_FOR K00_FOR L75_FOR MNO_FOR
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
OP0_FOR TAX_SUB LA_PAY CA_PAY OTH_COT VA_ADD INV_D_R1
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
INV_D_R2 INV_D_R3 INV_D_R4 INV_D_R5 INV_D_R6 INV_D_R7 INV_D_R8
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
INV_D_R9 INV_D_R10 INV_D_R11 INV_D_R12 HO_D_R1 HO_D_R2 HO_D_R3
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
HO_D_R4 HO_D_R5 HO_D_R6 HO_D_R7 HO_D_R8 HO_D_R9 HO_D_R10
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
HO_D_R11 HO_D_R12 GOV_D_R1 GOV_D_R2 GOV_D_R3 GOV_D_R4 GOV_D_R5
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
GOV_D_R6 GOV_D_R7 GOV_D_R8 GOV_D_R9 GOV_D_R10 GOV_D_R11 GOV_D_R12
"numeric" "numeric" "numeric" "numeric" "numeric" "numeric" "numeric"
FO_EXP
"numeric"
squared_data[is.na(squared_data)]=0
squared_data_dup4<-squared_data
Filling the matrix with actual values of the added columns
squared_data<-squared_data_dup4
start=12*20+1
end=start+24
squared_data[1:ncol(data)-1,start:end]<-as.numeric(t(data[start:end,2:ncol(data)]))
write.xlsx2(squared_data,"..\\data\\processed_morocco_MRIO.xlsx",sheetName = "IIOS",row.names = FALSE)